//
//  StockNewsService.m
//  Stock
//
//  Created by blacksun on 11-6-24.
//  Copyright 2011年 __MyCompanyName__. All rights reserved.
//

#import "StockNewsService.h"
#import "StockNews.h"
#import "CommonUtil.h"

@implementation StockNewsService

#pragma mark -service method
-(void)queryStockNewsWithUrl:(NSString *)urlStr ifNotExistInDBWithSectionId:(NSString *)aSectionId andSectionIndex:(int)aSectionIndex{
    NSMutableDictionary *userInfo = [NSMutableDictionary dictionaryWithObjectsAndKeys:kStockNewsServiceQueryStockNews,kASIHttpRequestUserinfoKey, nil];
    [userInfo setValue:aSectionId forKey:kStockNewsSectionIdKey];
    [userInfo setValue:[NSNumber numberWithInt:aSectionIndex] forKey:kStockNewsSectionIndexKey];
    
    if (kRecordPerRequest == [self countBySectionId:aSectionId andSectionIndex:aSectionIndex]) {
        NSArray *stockNewses = [self selectStockNewsBySectionId:aSectionId andSectionIndex:aSectionIndex];
        [self.delegate requestFinishedWithResult:stockNewses andUserinfo:userInfo];
    }else{
        NSURL *url = [[NSURL alloc] initWithString:urlStr];
        NSLog(@"%@",urlStr);
        ASIHTTPRequest *request = [[ASIHTTPRequest alloc] initWithURL:url];
        request.userInfo = userInfo;
        request.delegate = self;
        [request startAsynchronous];
        [self.workingRequests addObject:request];
        [url release];
        [request release];
    }
}
-(void)queryReportContentWithReportId:(NSString*)reportId{
    NSDictionary *userInfo = [NSDictionary dictionaryWithObjectsAndKeys:kStockNewsServiceQueryReportContent,kASIHttpRequestUserinfoKey, reportId,kStockNewsContentReportIdKey,nil];
    if ([self countStockNewsContentByReportId:reportId] != 0) {
        NSArray *contents = [self selectStockNewsContentByReportId:reportId];
        [self.delegate requestFinishedWithResult:contents andUserinfo:userInfo];
        return;
    }
    NSString *urlStr = [NSString stringWithFormat:@"%@report/detail?id=%@",kHostName,reportId];
    NSLog(@"%@",urlStr);
    NSURL *url = [NSURL URLWithString:urlStr];
    ASIHTTPRequest *request = [[ASIHTTPRequest alloc] initWithURL:url];
    request.userInfo = userInfo;
    request.delegate = self;
    [request startAsynchronous];
    [self.workingRequests addObject:request];
    [request release];
}


#pragma mark - access database
-(void)updateStockNews:(NSArray *)stockNews{
    if (!stockNews || [stockNews count]==0) {
        return;
    }
    StockNews *firstNews = [stockNews objectAtIndex:0];
    StockNews *lastedNews = [stockNews lastObject];
    [self deleteStockNewsBySectionId:firstNews.sectionId startSectionIndex:[firstNews.sectionIndex intValue] andEndSectionIndex:[lastedNews.sectionIndex intValue]];
    [self saveStockNewses:stockNews];
    //[self batchAndAsynchronousSaveObjects:stockNews];
}
-(void)saveStockNewses:(NSArray *)stockNewes{
    if (!stockNewes || [stockNewes count]==0) {
        return;
    }
    SQLiteInstanceManager *databaseManager = [SQLiteInstanceManager sharedManager];
    [databaseManager executeUpdateSQL:@"BEGIN TRANSACTION;"];
    sqlite3 *database = [databaseManager database];
    NSString *insertSql = [NSString stringWithFormat:@"insert into stock_news(report_id,section_id,section_index,source,author,pub_date,title) values(?,?,?,?,?,?,?);"];
    for (StockNews *news in stockNewes) {
        sqlite3_stmt *statement;
        if (sqlite3_prepare_v2(database, [insertSql UTF8String], -1, &statement, nil) != SQLITE_OK) {
            sqlite3_close(database);
            NSAssert(0,@"Error: failed to sqlite3_prepare_v2");
        }
        sqlite3_bind_text(statement, 1, [news.reportId UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 2, [news.sectionId UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_int64(statement, 3,[news.sectionIndex intValue] );
        sqlite3_bind_text(statement, 4, [news.source UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 5, [news.author UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 6, [news.pubDate UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 7, [news.title UTF8String], -1, SQLITE_TRANSIENT);
        if (sqlite3_step(statement) != SQLITE_DONE) {
            sqlite3_finalize(statement);
        }
        sqlite3_finalize(statement);
    }
    [databaseManager executeUpdateSQL:@"COMMIT;"];
}

-(NSInteger)countBySectionId:(NSString *)sectionId andSectionIndex:(int)sectionIndex{
    NSString *criteriaString = [[[NSString alloc] initWithFormat:@"where section_id='%@' and section_index<%d and section_index>=%d",sectionId,sectionIndex,sectionIndex-kRecordPerRequest] autorelease];
    return [super countWithTableName:@"stock_news" andCriteriaString:criteriaString];
}
-(NSArray *)selectStockNewsBySectionId:(NSString *)sectionId andSectionIndex:(int)sectionIndex{
    NSString *criteriaString = [NSString stringWithFormat:@" where section_id='%@' and section_index<%d and section_index>=%d",sectionId,sectionIndex,sectionIndex-kRecordPerRequest];
    
    SQLiteInstanceManager *databaseManager = [SQLiteInstanceManager sharedManager];
    sqlite3 *database = [databaseManager database];
    
	NSString *selectSql = [[NSString alloc] initWithFormat:@"SELECT stock_news.section_index,stock_news.section_id,stock_news.author,stock_news.report_id,stock_news.pub_date,stock_news.source,stock_news.title,%@.readed FROM stock_news LEFT OUTER JOIN %@ ON stock_news.report_id=%@.report_id ",[ReadStatus tableName],[ReadStatus tableName],[ReadStatus tableName]];
    
    NSString *fullSql = [selectSql stringByAppendingString:criteriaString];
    //NSLog(@"%@",fullSql);
    [selectSql release];
    
    NSMutableArray *result = [NSMutableArray array];
	
	sqlite3_stmt *statement;
	if(sqlite3_prepare_v2(database, [fullSql UTF8String], -1, &statement, nil) == SQLITE_OK){
		while (sqlite3_step(statement) == SQLITE_ROW) {
			StockNews *stockNews = [[StockNews alloc] init];
            stockNews.sectionIndex = [NSNumber numberWithInt:sqlite3_column_int64(statement, 0)];
            stockNews.sectionId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
            //stockNews.author = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
            stockNews.reportId = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
            stockNews.pubDate = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];
            stockNews.source = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)];
            stockNews.title = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 6)];
            if (1 == sqlite3_column_int64(statement,7)) {
                stockNews.readed = YES;
            }else{
                stockNews.readed = NO;
            }
            [result addObject:stockNews];
			[stockNews release];
		}
		sqlite3_finalize(statement);
		//sqlite3_close(database);
	}else {
		sqlite3_finalize(statement);
		//sqlite3_close(database);
		NSAssert(0,@"Error select feeds");
	}
    
	return result;
}

-(void)deleteStockNewsBySectionId:(NSString *)sectionId startSectionIndex:(int)start andEndSectionIndex:(int)end{
    NSString *criteriaString = [NSString stringWithFormat:@"delete from stock_news where section_id='%@' and section_index<=%d and section_index>=%d",sectionId,start,end];
    [[SQLiteInstanceManager sharedManager] executeUpdateSQL:criteriaString];
}


#pragma mark -StockNewsContent
-(NSInteger)countStockNewsContentByReportId:(NSString *)reportId{
    NSString *criteriaString = [NSString stringWithFormat:@"where  report_id='%@'" ,reportId];
    return [StockNewsContent countByCriteria:criteriaString];
}
-(NSArray *)selectStockNewsContentByReportId:(NSString *)reportId{
    NSString *criteriaString = [NSString stringWithFormat:@"where  report_id='%@'" ,reportId];
    return [StockNewsContent findByCriteria:criteriaString];
}

#pragma mark -delegate callback
- (void)requestFinished:(ASIHTTPRequest *)request{
    NSString *action = [request.userInfo objectForKey:kASIHttpRequestUserinfoKey];
    NSString *responseString = [request responseString];
    if ([kStockNewsServiceQueryStockNews isEqualToString:action]) {
        NSMutableArray *stockNewses = [NSMutableArray array];
        NSArray *reportDicts = [[responseString JSONValue] objectForKey:@"reports"];
        CommonUtil *commonUtil = [CommonUtil shareInstance];
        for (NSDictionary *reportDict in reportDicts) {
            StockNews *stockNews = [[StockNews alloc] init];
            [commonUtil copyMapEntries:reportDict toObject:stockNews];
            [stockNewses addObject:stockNews];
            [stockNews release];
        }
        [self updateStockNews:stockNewses];
        NSDictionary *userInfo = request.userInfo;
        NSNumber *sectionIndex = [userInfo objectForKey:kStockNewsSectionIndexKey];
        NSArray *result = [self selectStockNewsBySectionId:[userInfo objectForKey:kStockNewsSectionIdKey] andSectionIndex:[sectionIndex intValue]];
        [self.delegate requestFinishedWithResult:result andUserinfo:request.userInfo];
    }else if([kStockNewsServiceQueryReportContent isEqualToString:action]){
        NSMutableArray *reportContents = [NSMutableArray array];
        NSArray *reportDicts = [[responseString JSONValue] objectForKey:@"detail"];
        CommonUtil *commonUtil = [CommonUtil shareInstance];
        NSString *reportId = [request.userInfo objectForKey:kStockNewsContentReportIdKey];
        for (NSDictionary *contentDict in reportDicts) {
            StockNewsContent *content = [[StockNewsContent alloc] init];
            [commonUtil copyMapEntries:contentDict toObject:content];
            content.reportId = reportId;
            [reportContents addObject:content];
            [content release];
        }
        [super batchAndAsynchronousSaveObjects:reportContents];
        [self.delegate requestFinishedWithResult:reportContents andUserinfo:request.userInfo];
    }
}

- (void)requestFailed:(ASIHTTPRequest *)request{
    NSLog(@"request error");
    [self.delegate requestFinishedwithError:request.error andUserinfo:request.userInfo];
}

@end
